from flask import Blueprint, request, jsonify, make_response
from flask import current_app as app
from db import db1
from utils.comm import get_data, xelds
from xlrd import open_workbook
from io import BytesIO
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
import os
from utils.myfile import myf


se_user = Blueprint('comany', __name__)


@se_user.route('/test')
async def test():
    ste = "请求ip："+request.remote_addr+",请求的url"+request.url+"错误信息"+"\n"
    app.logger.error(ste)
    return 'ok'


@se_user.route('/oran', methods=['GET', 'POST'])
def oran():
    if request.method == 'POSt':
        pass
    else:
        sql = "select * from zuzhi"
        res = db1.findall(sql)
        red = xelds(res)
        return jsonify({'code': 200, 'list': red})


# 导入
@se_user.route('/upload', methods=['POST'])
def uploads():
    # 获取文件
    file = request.files['file']
    print(file)
    newname = file.rename(file.filename)
    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for j in range(0, ncols):
            print(row_date[j])
    return jsonify({'code': 200, 'msg': '成功'})


@se_user.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


# # 导出
# @se_user.route('/dwo', methods=['POST', 'GET'])
# def dwo():
#     out = BytesIO()
#     workbook = xlsxwriter.workbook(out)
#     table = workbook.add_worksheet()
#     # table.write(行, 列, 字段名)
#     # 行、列从 0 开始计数
#     # 字段命名根据每个人使用情况而定，插入数据部分也是
#     table.write(0, 0, "资产编号")
#     table.write(0, 1, "资产名称")
#
#     workbook.close()
#     filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
#     file = make_response(out.getvalue())
#     out.close()
#     mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
#     file.headers['Content-Type'] = mime_type
#     file.headers["Cache-Control"] = "no-cache"
#     file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
#     return file
#
#
# @se_user.route('/upload', methods=['POST'])
# def upload():
#     file = request.files['file']
#     newname = myf.rename(file.filename)
#     file.save(os.path.join('./static/files' + newname, 'w'))
#     name = 'static/files/' + newname
#     bk = open_workbook(name, encoding_override='utf8')
#     sh = bk.sheets()[0]
#     nrows = sh.nrows
#     ncols = sh.ncols
#     for i in range(1, nrows):
#         row_date = sh.row_values(i)
#         print(row_date[0])
#         print(row_date[1])
#     return 'ok'

